﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_Model;
using HCQ2_Model.ViewModel.SysAdmin;
using HCQ2_Model.SchoolClass;
using System.Globalization;
using HCQ2_Model.VLine.InputDto;
using HCQ2_Common;
using HCQ2_Common.SQL;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_UserDAL:HCQ2_IDAL.IT_UserDAL
    {
        /// <summary>
        ///  参数
        /// </summary>
        private Dictionary<string, object> _param = new Dictionary<string, object>();
        private StringBuilder sb = new StringBuilder();
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">用户对象</param>
        public void AddUser(T_User user)
        {
            base.Add(user);
        }
        /// <summary>
        ///  编辑用户
        /// </summary>
        /// <param name="user">用户</param>
        public void EditUser(T_User user,string selUnit)
        {
            user.update_date = DateTime.Now;
            if (string.IsNullOrEmpty(selUnit))
                Modify(user, s => s.user_id == user.user_id, "user_name", "login_name", "user_sex", "status", "max_machine",
                "user_qq", "user_email", "user_phone", "user_address", "user_birth", "user_note", "user_type", "user_identify", "user_unit", "unit_code", "update_date", "use_type");
            else
                Modify(user, s => s.user_id == user.user_id, "user_name", "login_name", "user_sex", "status", "max_machine",
                "user_qq", "user_email", "user_phone", "user_address", "user_birth", "user_note", "user_type", "user_identify", "user_unit", "unit_code", "update_date", "use_type");
        }

        public int GetCountByData(string keyword, int folder_id,string orgUnit)
        {
            sb?.Clear();
            sb.Append("SELECT COUNT(0)AS num FROM dbo.T_User WHERE 1=1 ");
            //关键字
            if (!string.IsNullOrEmpty(keyword))
                sb.Append($" AND (login_name LIKE '%{keyword}%' OR user_name LIKE '%{keyword}%' OR user_phone LIKE '%{keyword}%') ");
            //单位
            if (!string.IsNullOrEmpty(orgUnit))
                sb.Append($" AND unit_code='{orgUnit}' ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }

        public List<UserModel> GetUserLimt(string keyword,string orgUnit, int page, int rows, string inDate, string outDate, string roleCode, int status = -1)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($@"select top {rows} * from(
SELECT users.*,limuser.id,ROW_NUMBER() OVER(order by users.user_id) as DispOrder FROM
(SELECT *,CONVERT(varchar(100),create_date,120) AS register_date,CONVERT(varchar(100),update_date,120) AS modify_date FROM dbo.T_User where user_name<>'' ");     
            if (!string.IsNullOrEmpty(keyword))
                sb.Append(string.Format(" and (login_name like '%{0}%' or user_name like '%{0}%' or user_phone like '%{0}%') ", keyword));
            //状态
            if (status > -1)
                sb.Append($" and status={status} ");
            if (!string.IsNullOrEmpty(orgUnit))
                sb.AppendFormat(" and unit_code='{0}' ", orgUnit);
            //注册日期
            if (!string.IsNullOrEmpty(inDate))
                inDate = DateTime.ParseExact(inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(outDate))
                outDate = DateTime.ParseExact(outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date>='{inDate}' AND create_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date<='{outDate}' ");
            sb.Append(@")  users LEFT JOIN 
            (SELECT id, user_id FROM dbo.T_LimitUser) limuser ON users.user_id = limuser.user_id  ");
            //是否根据角色代码查询
            if (!string.IsNullOrEmpty(roleCode))
                sb.Append($@"INNER JOIN
                (SELECT r.role_code,rel.user_id FROM 
	                (SELECT b1.role_id,a1.user_id FROM (SELECT group_id,user_id FROM dbo.T_UserGroupRelation) a1 INNER JOIN 
	                (SELECT role_id, group_id FROM dbo.T_RoleGroupRelation) b1 ON a1.group_id = b1.group_id 
	                UNION 
	                SELECT role_id,user_id FROM dbo.T_UserRoleRelation 
	                )rel INNER JOIN
	                (SELECT role_id,role_code FROM dbo.T_Role WHERE role_code='{roleCode}')r ON r.role_id = rel.role_id
                )r ON r.user_id = users.user_id ");
            sb.Append($@"WHERE users.user_id NOT IN(SELECT user_id FROM dbo.T_Org_User) 
            )as A where A.DispOrder>{ (page - 1) * rows}");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserModel>(dt,true);
            //Linq写法
            //var query =
            //    (from o in base.db.Set<T_User>()
            //        join limuser in db.Set<T_LimitUser>() on o.user_id equals limuser.user_id
            //        select new
            //        {
            //            o.user_id,o.user_name,o.login_name,o.user_pwd,o.user_sex,o.user_qq,
            //            o.user_email,o.user_phone,o.user_address,o.user_birth,o.user_note,limuser.id,
            //            limuser.from_time,limuser.to_time
            //        }).ToList();
            //return query;
        }
        /// <summary>
        ///  实现根据组织机构获取用户
        /// </summary>
        /// <param name="unitid"></param>
        /// <param name="keyword"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public List<T_User> GetUserByUnitID(int folder_id, string keyword,string orgUnit, int page, int rows, string inDate, string outDate, string roleCode, int status=-1)
        {
            if (folder_id<=0)
                return null;
            sb?.Clear();
            sb.AppendFormat($"SELECT TOP {rows} users.* FROM (SELECT * FROM dbo.T_User WHERE 1=1 ");
            //关键字 用户名、登录名、手机号
            if (!string.IsNullOrEmpty(keyword))
                sb.AppendFormat($" AND (user_name LIKE '%{keyword}%' OR login_name LIKE '%{keyword}%' OR user_phone LIKE '%{keyword}%') ");
            //状态
            if (status > -1)
                sb.Append($" AND status={status} ");
            //单位
            if (!string.IsNullOrEmpty(orgUnit))
                sb.AppendFormat(" AND unit_code='{0}' ", orgUnit);
            //注册日期
            if (!string.IsNullOrEmpty(inDate))
                inDate = DateTime.ParseExact(inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(outDate))
                outDate = DateTime.ParseExact(outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date>='{inDate}' AND create_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND create_date<='{outDate}' ");
            sb.AppendFormat($@") users INNER JOIN
            (SELECT ROW_NUMBER() OVER(ORDER BY user_id ASC) AS rowNumber,user_id FROM dbo.T_Org_User WHERE UnitID 
            IN(SELECT folder_id FROM dbo.T_OrgFolder WHERE folder_path LIKE (SELECT folder_path FROM T_OrgFolder WHERE folder_id={folder_id})+'%')) org ON
            org.user_id = users.user_id WHERE org.rowNumber>{(page - 1) * rows};");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_User>(dt);
        }

        /// <summary>
        /// 通过学校名称获取下面的用户，用于在添加班级时设置班主任
        /// </summary>
        /// <param name="schoolName">学校名称</param>
        /// <returns></returns>
        public List<TeacherSelectList> ListTeacherSelect(string schoolName)
        {
            string sql = string.Format("select USER_ID userId,USER_NAME userName,login_name loginName from T_User where user_unit='{0}'",schoolName);

            List<TeacherSelectList> result = HCQ2_Common.SQL.SqlHelper.ExecuteList<TeacherSelectList>(sql);

            return result;
        }
        /// <summary>
        ///  获取用户详细信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public List<T_User> GetUserList(string userid)
        {
            sb.Append($"select * from T_User where user_id in(${userid});");
            return SqlHelper.ExecuteList<T_User>(sb.ToString());
        }

        /// <summary>
        ///  获取登录总次数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetLoginNum(LoginNumInputDto dto)
        {
            sb?.Clear();
            sb.Append("SELECT ISNULL(SUM(login.login_count),0)AS login_count FROM (SELECT user_id FROM dbo.T_User WHERE  status = 1 ");
            //是否个人数据
            if (dto.isPerson>0 && !string.IsNullOrEmpty(dto.userid))
                sb.Append($" AND user_guid='{dto.userid}' ");
            //单位代码
            if(!string.IsNullOrEmpty(dto.unitCode))
                sb.Append($" AND unit_code='{dto.unitCode}' ");
            sb.Append(") users INNER JOIN (SELECT user_id,COUNT(user_id)AS login_count FROM dbo.T_LoginLog WHERE 1 = 1 ");
            //时间
            if (!string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND login_date>= '{0}' AND login_date<='{1}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(dto.startDate) && string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND login_date>= '{0}' ", DateTime.ParseExact(dto.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(dto.startDate) && !string.IsNullOrEmpty(dto.endDate))
                sb.AppendFormat(" AND login_date<= '{0}' ", DateTime.ParseExact(dto.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.Append(" GROUP BY user_id)login ON login.user_id = users.user_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取系统用户数
        /// </summary>
        /// <param name="unitDto"></param>
        /// <returns></returns>
        public int GetSysUserNum(SysUserInputDto unitDto)
        {
            sb?.Clear();
            sb.Append("SELECT COUNT(0)AS num FROM dbo.T_User WHERE status=1 ");
            //单位代码
            if (!string.IsNullOrEmpty(unitDto.unitCode))
                sb.Append($" AND unit_code='{unitDto.unitCode}' ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取系统在线人数
        /// </summary>
        /// <param name="unitDto"></param>
        /// <returns></returns>
        public int GetSysLineUserNum(SysUserInputDto unitDto)
        {
            sb?.Clear();
            sb.Append("SELECT COUNT(0)AS num FROM dbo.T_Login WHERE DATEDIFF(MINUTE,ISNULL(last_time,0),GETDATE())<=60  ");
            //单位代码
            if (!string.IsNullOrEmpty(unitDto.unitCode))
                sb.Append($" AND user_id IN(SELECT user_id FROM dbo.T_User WHERE unit_code='{unitDto.unitCode}') ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}